﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;

namespace DelonixRegiaHotel.Classes
{
    public class DBManager
    {
        public static HotelUser GetUserByName(string name)
        {
            HotelUser user = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM HotelUser WHERE name=@name";
                comm.Parameters.AddWithValue("@name", name);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    user = new HotelUser();
                    user.Username = (string)dr["username"];
                    user.Password = (string)dr["password"];
                    user.Name = (string)dr["name"];
                    user.Date_of_birth = (string)dr["date_of_birth"];
                    user.Bank_account_number = (string)dr["bank_account_number"];
                    user.Home_address = (string)dr["home_address"];
                    user.Tel_num = (string)dr["tel_num"];
                    user.Rank = (string)dr["rank"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return user;
        }

        public static int createAdmin(HotelUser u)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO HotelUser(username, password, name, date_of_birth, bank_account_number, home_address, tel_num, rank) VALUES(@username, @password, @name, @date_of_birth, @bank_account_number, @home_address, @tel_num, @rank)";
                comm.Parameters.AddWithValue("@username", u.Username);
                comm.Parameters.AddWithValue("@password", u.Password);
                comm.Parameters.AddWithValue("@name", u.Name);
                comm.Parameters.AddWithValue("@date_of_birth", u.Date_of_birth);
                comm.Parameters.AddWithValue("@bank_account_number", u.Bank_account_number);
                comm.Parameters.AddWithValue("@home_address", u.Home_address);
                comm.Parameters.AddWithValue("@tel_num", u.Tel_num);
                comm.Parameters.AddWithValue("@rank", u.Rank);

                rowsinserted = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowsinserted;
        }

        public static int deleteAdmin(string u)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM HotelUser WHERE username=@username";
                comm.Parameters.AddWithValue("@username", u);
                rowsdeleted = comm.ExecuteNonQuery();
             }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowsdeleted;
        }

        public static int updateAdmin(HotelUser u)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE HotelUSer SET password=@password, name=@name, date_of_birth=@date_of_birth, bank_account_number=@bank_account_number, home_address=@home_address, tel_num=@tel_num, rank=@rank WHERE username=@username";

                comm.Parameters.AddWithValue("@username", u.Username);
                comm.Parameters.AddWithValue("@password", u.Password);
                comm.Parameters.AddWithValue("@name", u.Name);
                comm.Parameters.AddWithValue("@date_of_birth", u.Date_of_birth);
                comm.Parameters.AddWithValue("@bank_account_number", u.Bank_account_number);
                comm.Parameters.AddWithValue("@home_address", u.Home_address);
                comm.Parameters.AddWithValue("@tel_num", u.Tel_num);
                comm.Parameters.AddWithValue("@rank", u.Rank);

                rowsinserted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowsinserted;
            }

        public static int createGuest(HotelGuest g)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO HotelGuest(first_name, last_name, phone_num, email, check_in_date, check_out_date, check_in_time, check_out_time, adult, child, home_address, mailing_address, postal_code, room_type, bed_sizes, smoking_room, credit_card_info, room_num, check_in_by, total_bill, housekeeper_name) VALUES(@first_name, @last_name, @phone_num, @email, @check_in_date, @check_out_date, @check_in_time, @check_out_time, @adult, @child, @home_address, @mailing_address, @postal_code, @room_type, @bed_sizes, @smoking_room, @credit_card_info, @room_num, @check_in_by, @total_bill, @housekeeper_name)";
                comm.Parameters.AddWithValue("@first_name", g.First_name);
                comm.Parameters.AddWithValue("@last_name", g.Last_name);
                comm.Parameters.AddWithValue("@phone_num", g.Phone_num);
                comm.Parameters.AddWithValue("@email", g.Email);
                comm.Parameters.AddWithValue("@check_in_date", g.Check_in_date);
                comm.Parameters.AddWithValue("@check_out_date", g.Check_out_date);
                comm.Parameters.AddWithValue("@check_in_time", g.Check_in_time);
                comm.Parameters.AddWithValue("@check_out_time", g.Check_out_time);
                comm.Parameters.AddWithValue("@adult", g.Adult);
                comm.Parameters.AddWithValue("@child", g.Child);
                comm.Parameters.AddWithValue("@home_address", g.Home_address);
                comm.Parameters.AddWithValue("@mailing_address", g.Mailing_address);
                comm.Parameters.AddWithValue("@postal_code", g.Postal_code);
                comm.Parameters.AddWithValue("@room_type", g.Room_type);
                comm.Parameters.AddWithValue("@bed_sizes", g.Bed_sizes);
                comm.Parameters.AddWithValue("@smoking_room", g.Smoking_room);
                comm.Parameters.AddWithValue("@credit_card_info", g.Credit_card_info);
                comm.Parameters.AddWithValue("@room_num", g.Room_num);
                comm.Parameters.AddWithValue("@check_in_by", g.Check_in_by);
                comm.Parameters.AddWithValue("@total_bill", g.Total_bill);
                comm.Parameters.AddWithValue("@housekeeper_name", g.Housekeeper_name);

                rowsinserted = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowsinserted;
        }
        }
        }
    
